package com.example.studentmanagement2.repository.JdbcRepository;

import com.example.studentmanagement2.model.Course;
import com.example.studentmanagement2.repository.Repository.CourseRepository;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;

@Repository
public class JdbcCourseRepository implements CourseRepository {
    private final JdbcTemplate jdbcTemplate;

    public JdbcCourseRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Course> findAll() {
        return jdbcTemplate.query(
                "SELECT * FROM courses",
                (rs, rowNum) -> new Course(
                        rs.getString("course_id"),
                        rs.getString("course_name"),
                        rs.getInt("credit"),
                        rs.getString("teacher")
                )
        );
    }

    @Override
    public Optional<Course> findById(String courseId) {
        try {
            return jdbcTemplate.queryForObject(
                    "SELECT * FROM courses WHERE course_id = ?",
                    new Object[]{courseId},
                    (rs, rowNum) -> Optional.of(new Course(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getInt("credit"),
                            rs.getString("teacher")
                    ))
            );
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }

    @Override
    public Course save(Course course) {
        jdbcTemplate.update(
                "INSERT INTO courses (course_id, course_name, credit, teacher) VALUES (?, ?, ?, ?)",
                course.getCourseId(),
                course.getCourseName(),
                course.getCredit(),
                course.getTeacher()
        );
        return course;
    }

    // JdbcCourseRepository.java
    @Override
    public void update(String originalCourseId, Course course) {
        // 1. 更新课程表的主键和字段
        jdbcTemplate.update(
                "UPDATE courses SET course_id = ?, course_name = ?, credit = ?, teacher = ? WHERE course_id = ?",
                course.getCourseId(),
                course.getCourseName(),
                course.getCredit(),
                course.getTeacher(),
                originalCourseId  // 原始课程编号
        );

        // 2. 级联更新关联表（如 grades）
        jdbcTemplate.update(
                "UPDATE grades SET course_id = ? WHERE course_id = ?",
                course.getCourseId(),  // 新课程编号
                originalCourseId       // 原始课程编号
        );
    }

    @Override
    public void deleteById(String courseId) {
        jdbcTemplate.update("DELETE FROM courses WHERE course_id = ?", courseId);
    }
    @Override
    public List<Course> search(String keyword) {
        String sql = "SELECT * FROM courses WHERE " +
                "course_id LIKE ? OR course_name LIKE ?";
        String pattern = "%" + keyword + "%";
        return jdbcTemplate.query(
                sql,
                new Object[]{pattern, pattern},
                (rs, rowNum) -> new Course(
                        rs.getString("course_id"),
                        rs.getString("course_name"),
                        rs.getInt("credit"),
                        rs.getString("teacher")
                )
        );
    }

    @Override
    public boolean existsById(String courseId) {
        String sql = "SELECT COUNT(*) FROM courses WHERE course_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, courseId);
        return count != null && count > 0;
    }
}